Creating Your First Database and Verifying the Setup – Complete MySQL Notes

Creating your first database is one of the most important steps in learning MySQL. A database provides an organized environment for storing and managing information used by websites, business applications, reporting systems, and data analytics projects. In MySQL, you can create a database using SQL commands or through the graphical interface of MySQL Workbench. After creating it, you should verify that the database exists, select it for use, create a sample table, insert records, and run queries to confirm that everything works correctly.

Explore the JustAcademy MySQL Training Course and Register for a MySQL Course Demo to learn more about database development and SQL.

1. Learning Objectives

After completing this topic, you will be able to:

• Understand the meaning and purpose of a database.
• Identify the basic components of a MySQL database environment.
• Connect to MySQL Server using MySQL Workbench or the command-line client.
• Create your first database using the CREATE DATABASE statement.
• Verify database creation using SHOW DATABASES.
• Select a database using the USE statement.
• Create tables and insert sample records.
• Verify the database, table structure, and stored data.
• Understand common database creation errors and their solutions.

2. What Is a Database?

A database is an organized collection of related information stored electronically so that it can be accessed, managed, updated, and retrieved efficiently.

For example, an educational institute may maintain a database containing student details, course information, fees, attendance, and examination results.

Example of Student Information

In a relational database, information is generally organized into tables. Each table contains columns that define the type of information and rows that represent individual records.

Why Do We Need Databases?

• To store large amounts of information in an organized manner.
• To retrieve information quickly using queries.
• To update or delete records when required.
• To reduce unnecessary data duplication through suitable database design.
• To control access to information through permissions.
• To support applications, websites, reports, and analytics.

3. What Is MySQL?

MySQL is a relational database management system (RDBMS) that uses SQL, or Structured Query Language, to create databases, define tables, manage records, and retrieve information.

MySQL Server processes SQL statements and manages the underlying database storage. MySQL Workbench is a graphical application that helps users connect to MySQL Server, execute SQL queries, and manage database objects.

Example

CREATE DATABASE college_db;

This statement asks MySQL Server to create a database named college_db, provided the connected account has the required privileges and a database with that name does not already exist.

Common Uses of MySQL

• Website and web application development.
• E-commerce product and order management.
• Student information systems.
• Employee and payroll applications.
• Inventory and sales management.
• Business reporting and data analysis.

4. Understanding the MySQL Database Environment

Before creating a database, it is useful to understand the main components involved in the process.

5. Prerequisites Before Creating Your First Database

Make sure the following requirements are satisfied before proceeding.

• MySQL Server is installed on your computer or accessible on a database server.
• The MySQL service is running.
• You have a valid MySQL username and password or another configured authentication method.
• Your account has permission to create a database.
• You have MySQL Workbench or the MySQL command-line client available, if you plan to use one of these tools.

Check the MySQL Server Connection

In MySQL Workbench, open your saved connection and authenticate with your configured credentials. If you are using the command-line client, enter:

mysql -u root -p

Press Enter and provide the password when prompted. The username root is only an example; use the account configured for your installation.

If the connection succeeds, you can execute SQL statements in the MySQL client. If it fails, check whether the server is running and verify the connection details and credentials.

6. Checking Existing Databases

Before creating a new database, you can inspect the databases visible to your account. This helps you avoid confusion and understand the current server environment.

SQL Command

SHOW DATABASES;

Explanation

• SHOW requests information from MySQL.
• DATABASES specifies that you want to list databases.
• The semicolon marks the end of the SQL statement.

Example Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

This is an illustrative example. The actual list depends on your installation, permissions, and databases already created on the server. Some databases may not be visible to an account without the required privileges.

7. Creating Your First Database Using SQL

The CREATE DATABASE statement creates a new database on the connected MySQL Server.

Basic Syntax

CREATE DATABASE database_name;

Replace database_name with a suitable name for your database.

Example: Create a College Database

CREATE DATABASE college_db;

Explanation

• CREATE tells MySQL to create a database object.
• DATABASE specifies the object type.
• college_db is the name of the database being created.
• ; marks the end of the statement.

Expected Result

Query OK, 1 row affected

The exact timing and output format may vary by client and MySQL version. A successful response indicates that the statement completed successfully.

Important Naming Rules

• Choose a clear and meaningful database name.
• Avoid spaces in database names; use underscores when needed.
• Avoid names that conflict with reserved words or create ambiguity.
• Use consistent letter casing, especially when working across different operating systems.
• Follow your organization's naming conventions for production databases.

8. Using CREATE DATABASE IF NOT EXISTS

If a database already exists, running a basic CREATE DATABASE statement with the same name normally produces an error. You can use IF NOT EXISTS to avoid that duplicate-name error.

Syntax

CREATE DATABASE IF NOT EXISTS database_name;

Example

CREATE DATABASE IF NOT EXISTS college_db;

This statement creates college_db if it does not exist. If it already exists, MySQL does not create a duplicate database. A note may be reported instead.

Important: IF NOT EXISTS does not reset an existing database, validate that its tables are correct, or replace its existing structure. It only avoids an error when the database name already exists.

9. Verifying That the Database Was Created

After creating the database, verify its existence before using it.

Step 1: Display the Database List

SHOW DATABASES;

Step 2: Find Your Database

Look for college_db in the output.

Example Output

+--------------------+
| Database           |
+--------------------+
| college_db         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

The output is only an example. Your server may contain additional databases or a different set of system databases.

Step 3: Inspect the Database Definition

You can also view the database definition with:

SHOW CREATE DATABASE college_db;

This statement returns the SQL definition of the database, including its configured character set and collation when applicable.

Why Is Verification Important?

• It confirms that the database creation statement succeeded.
• It helps identify spelling mistakes in the database name.
• It confirms that the database is visible to your account.
• It provides an early check before you create tables or insert records.

10. Selecting the Database Using USE

Creating a database does not automatically make it the current database. You must select it before running statements that rely on the current database.

Syntax

USE database_name;

Example

USE college_db;

Expected Result

Database changed

After this command succeeds, subsequent statements that do not specify a database name will generally operate within college_db, provided they use objects in the current database.

Verify the Selected Database

SELECT DATABASE();

Example Output

+----------------+
| DATABASE()     |
+----------------+
| college_db     |
+----------------+

If the result is NULL, no default database is selected for the current session.

11. Creating Your First Table

A newly created database is initially empty. To store structured records, create a table inside it.

Example: Create a Students Table

USE college_db;
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    course VARCHAR(100),
    city VARCHAR(100)
);

Explanation of the Columns

Verify That the Table Exists

SHOW TABLES;

View the Table Structure

DESCRIBE students;

The DESCRIBE statement displays the table's columns, data types, nullability, key information, defaults, and other structural details.

12. Inserting Sample Records

After creating a table, use the INSERT INTO statement to add sample records.

Example SQL

INSERT INTO students (student_name, email, course, city)
VALUES
('Rahul Sharma', '[email protected]', 'MySQL', 'Mumbai'),
('Priya Patel', '[email protected]', 'Python', 'Pune'),
('Amit Verma', '[email protected]', 'Data Analytics', 'Delhi');

Explanation

• INSERT INTO students identifies the table receiving the records.
• The column list identifies the fields into which values will be inserted.
• VALUES provides the values for each record.
• Text values are enclosed in single quotation marks.
• Because student_id uses AUTO_INCREMENT, MySQL generates the identifiers automatically when they are omitted.

The email addresses are sample values for practice, not real contact details.

13. Retrieving and Verifying the Inserted Data

Use a SELECT statement to retrieve records from your table.

Display All Records

SELECT * FROM students;

Example Output

The generated identifier values may differ if you have inserted other records or used the table previously.

Select Specific Columns

SELECT student_name, course
FROM students;

Filter Records Using WHERE

SELECT *
FROM students
WHERE city = 'Mumbai';

This query returns records whose city value matches Mumbai according to the column's comparison rules and collation.

Count the Records

SELECT COUNT(*) AS total_students
FROM students;

If the three sample records were inserted successfully and no other records were added, the result should be 3.

14. Verifying the Setup with Additional SQL Queries

Use the following queries to confirm that the database and its objects are available and that the current session is working as expected.

Check the MySQL Server Version

SELECT VERSION();

This query returns the version of the connected MySQL Server.

Check the Current Database

SELECT DATABASE();

The expected result is college_db if you have selected that database in the current session.

List the Tables in the Current Database

SHOW TABLES;

You should see students if the table exists and is visible in the selected database.

Inspect the Table Definition

SHOW CREATE TABLE students;

This command displays the SQL definition used for the table, including its columns and constraints.

Check the Number of Rows

SELECT COUNT(*) AS total_students
FROM students;

Use this query to confirm that the expected number of sample records exists.

15. Creating Your First Database Using MySQL Workbench

MySQL Workbench provides a graphical interface for database creation and administration. The following steps assume that MySQL Workbench is installed and can connect to a running MySQL Server.

Step 1: Open MySQL Workbench

Launch MySQL Workbench from your computer's application menu.

Step 2: Open Your MySQL Connection

Select your saved connection, enter credentials if requested, and confirm that the SQL Editor opens.

Step 3: Open a New SQL Query Tab

Click the new SQL tab button and enter the following command:

CREATE DATABASE college_db;

Step 4: Execute the SQL Statement

Click the Execute button, commonly represented by a lightning-bolt icon, or use the relevant keyboard shortcut for your version.

Step 5: Refresh the Schemas Panel

In the Navigator area, locate the Schemas panel and refresh it if necessary. The new database should appear as college_db.

Step 6: Select the Database

Run the following query in the SQL Editor:

USE college_db;
SELECT DATABASE();

Step 7: Create a Table

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course VARCHAR(100)
);

Step 8: Verify the Table

SHOW TABLES;
DESCRIBE students;

Note: MySQL Workbench is a client application. Creating a database through Workbench sends SQL statements to the MySQL Server; Workbench itself is not the database server.

16. Database Creation Using CREATE SCHEMA

In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE. Both statements create a database.

Example

CREATE SCHEMA training_db;

You can verify the result with:

SHOW DATABASES;

For beginners, CREATE DATABASE is a clear and commonly used choice. You may encounter either form in MySQL examples and tools.

17. Common Errors and Their Solutions

Error 1: Database Already Exists

Cause: A database with the requested name is already present on the server.

Solution: Check the existing databases or use the following statement when you want to create the database only if it does not already exist:

CREATE DATABASE IF NOT EXISTS college_db;

Do not drop an existing database simply to remove the error unless you understand the consequences and have confirmed that its data can safely be deleted.

Error 2: Access Denied

Cause: The connected MySQL account does not have sufficient privileges to create the database.

Solution: Ask the database administrator to provide the appropriate permissions or use an authorized account. Do not assume every account can create databases.

Error 3: No Database Selected

Cause: A query references a table without selecting the database or qualifying the table name with its database.

Solution: Select the database before running the query.

USE college_db;
SELECT * FROM students;

Error 4: Unknown Database

Cause: The database name is incorrect, the database does not exist, or it is not available to the connected account.

Solution: Run SHOW DATABASES;, check the spelling, and confirm that you are connected to the intended MySQL Server.

Error 5: MySQL Server Is Not Running

Cause: The MySQL Server service is stopped or failed to start.

Solution: Check the service status, start the service if appropriate, and inspect the error logs if startup fails.

Error 6: Table Already Exists

Cause: The table name is already used in the selected database.

Solution: Check the existing tables before creating another one. If appropriate, use:

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course VARCHAR(100)
);

This statement does not modify the structure of an existing table to match the definition shown.

18. Best Practices for Creating and Verifying Databases

• Use meaningful and consistent database names.
• Check the database list before creating a new database.
• Use IF NOT EXISTS when appropriate for repeatable setup scripts.
• Select the intended database explicitly using USE.
• Use appropriate data types and constraints when creating tables.
• Verify database creation using SHOW DATABASES.
• Verify the selected database using SELECT DATABASE().
• Verify table creation using SHOW TABLES and DESCRIBE.
• Use SELECT queries to confirm that records were inserted correctly.
• Use authorized accounts with only the privileges needed for the task.
• Avoid using the administrative root account for routine application operations.
• Never store real passwords or confidential information in practice examples.
• Back up important data before making destructive changes.
• Use a development database for learning and testing instead of experimenting on production data.

19. Complete Practical Example

The following script creates a practice database, defines a table, inserts sample records, and verifies the setup. Run it on a MySQL Server where you have permission to create databases.

-- Step 1: Create the database
CREATE DATABASE IF NOT EXISTS college_db;

-- Step 2: Select the database
USE college_db;

-- Step 3: Create the students table
CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    course VARCHAR(100),
    city VARCHAR(100)
);

-- Step 4: Insert sample records
INSERT INTO students (student_name, email, course, city)
VALUES
('Rahul Sharma', '[email protected]', 'MySQL', 'Mumbai'),
('Priya Patel', '[email protected]', 'Python', 'Pune'),
('Amit Verma', '[email protected]', 'Data Analytics', 'Delhi');

-- Step 5: Verify the database
SHOW DATABASES;

-- Step 6: Verify the selected database
SELECT DATABASE();

-- Step 7: Verify the tables
SHOW TABLES;

-- Step 8: Inspect the table structure
DESCRIBE students;

-- Step 9: Display the stored records
SELECT * FROM students;

-- Step 10: Count the records
SELECT COUNT(*) AS total_students
FROM students;

Important: The script uses CREATE TABLE IF NOT EXISTS, so it will not recreate an existing table. However, the INSERT statements will add the sample rows each time the script is run. Repeated runs can therefore create duplicate sample records. For repeatable practice, clear or reset a disposable practice table carefully, or use a unique constraint and an appropriate insertion strategy.

Expected Verification Results

• The database college_db appears in the database list.
• SELECT DATABASE() returns college_db after it is selected.
• The table students appears in the table list.
• DESCRIBE students displays the expected columns.
• SELECT * FROM students returns the records stored in the table.
• The count query returns the number of rows currently present in the table.

20. Interview Questions and Answers

Q1. What is a database?

A database is an organized collection of related information that can be stored, managed, and retrieved electronically.

Q2. What is MySQL?

MySQL is a relational database management system that uses SQL to manage and query data.

Q3. Which SQL statement creates a database?

The CREATE DATABASE statement creates a database.

CREATE DATABASE college_db;

Q4. How do you list databases in MySQL?

Use the SHOW DATABASES; statement to display databases visible to your account.

Q5. Does creating a database automatically select it?

No. Use USE database_name; to select the database for the current session.

Q6. How do you check the currently selected database?

Execute SELECT DATABASE();. It returns the current database name or NULL if no default database is selected.

Q7. What is the purpose of IF NOT EXISTS?

It prevents a duplicate-name error when the database already exists. It does not reset or repair the existing database.

Q8. How can you verify that a table exists?

Use SHOW TABLES; after selecting the relevant database.

Q9. How can you inspect a table's columns?

Use DESCRIBE table_name; to view column definitions and related structural details.

Q10. What is the difference between a database and a table?

A database is the overall environment that contains database objects, while a table stores structured records in rows and columns.

Q11. What does SELECT VERSION() do?

It returns the version of the MySQL Server to which the current session is connected.

Q12. Why might a user be unable to create a database?

The MySQL account may lack the required database-creation privilege, or the connection may be to an unintended server.

21. Practice Exercises

1. Connect to your MySQL Server using MySQL Workbench or the command-line client.
2. Display the existing databases using SHOW DATABASES;.
3. Create a database named training_db.
4. Verify that training_db appears in the database list.
5. Select it using USE training_db;.
6. Confirm the current database using SELECT DATABASE();.
7. Create a table named employees with columns for employee ID, employee name, department, and city.
8. Insert at least three sample employee records.
9. Use SHOW TABLES; and DESCRIBE employees; to verify the table.
10. Retrieve all employee records using SELECT * FROM employees;.
11. Use COUNT(*) to count the number of employee records.
12. Write down the solution for an access-denied error when creating a database.

22. Quick Revision Cheat Sheet

23. Official MySQL Learning Resources

• MySQL Reference Manual – Creating and Selecting a Database
• MySQL Reference Manual – Creating and Using a Database
• Getting Started with MySQL – Official Guide

Conclusion

Creating your first database and verifying the setup is a foundational MySQL skill. Start by connecting to the MySQL Server, inspect existing databases, create a new database, and select it with the USE statement. Then create a table, insert sample records, and verify the result using SHOW DATABASES, SHOW TABLES, DESCRIBE, and SELECT queries. Following these steps helps you confirm that the server connection, database, table structure, and stored data are working as expected.

Continue developing your SQL and database administration skills with the JustAcademy MySQL Training Course. To learn more about the course, register for a MySQL Course Demo.